﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using CrazyTalk.Common;
using CrazyTalk.Data;
using CrazyTalk.IDAL;
using CrazyTalk.Models;

namespace CrazyTalk.DAL
{
    /// <summary>
    /// 数据访问类:PermissionListDAL
    /// </summary>
    public class PermissionDAL : IPermissionDAL
    {
        public PermissionDAL()
        { }
        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("RoleId", "PermissionList");
        }
        /// <summary>
        /// 根据输入的用户名和角色编号，获取用户的权限列表
        /// </summary>
        /// <param name="userId">用户名</param>
        /// <param name="roleId">角色编号</param>
        /// <returns></returns>
        public DataTable GetPermissionList(string userId, int roleId)
        {
            return DbHelperSQL.GetDataTableByPro("Query_Permission_List"
                , new SqlParameter("@UserId", SqlDbType.VarChar, 20) { Value = userId }
                , new SqlParameter("@RoleId", SqlDbType.Int) { Value = roleId });
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int RoleId, string MenuID, int FuncID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from PermissionList");
            strSql.Append(" where RoleId=@RoleId and MenuID=@MenuID and FuncID=@FuncID ");
            SqlParameter[] parameters = {
					new SqlParameter("@RoleId", SqlDbType.Int,4),
					new SqlParameter("@MenuID", SqlDbType.NVarChar,32),
					new SqlParameter("@FuncID", SqlDbType.Int,4)			};
            parameters[0].Value = RoleId;
            parameters[1].Value = MenuID;
            parameters[2].Value = FuncID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Permission model)
        {
            
                return false;
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Permission model)
        {
            
                return false;
            
        }
        /// <summary>
        /// 更新权限控制
        /// </summary>
        /// <param name="lsNewValue">新增权限值列</param>
        /// <param name="lsDelValue">删除权限值列</param>
        /// <param name="roleId">涉及角色编号</param>
        /// <returns></returns>
        public bool UpdatePermission(List<string> lsNewValue, List<string> lsDelValue, int roleId)
        {
            List<SqlParameter> lsParm = new List<SqlParameter>();
            StringBuilder sbSql = new StringBuilder();
            int parmIndex = 0;

            if (lsNewValue != null && lsNewValue.Count > 0)
            {
                string[] values = null;
                foreach (string item in lsNewValue)
                {
                    values = item.Split('_');
                    if (values.Length != 2)
                    {
                        return false;
                    }
                    sbSql.AppendFormat("if not exists(select * from PermissionList where MenuID=@MenuID{0} and FuncID=@FuncID{0} and RoleId=@RoleId) insert into PermissionList (MenuID,FuncID,RoleId) values(@MenuID{0},@FuncID{0},@RoleId);"
                    , parmIndex);
                    lsParm.Add(new SqlParameter("@MenuID" + parmIndex, SqlDbType.NVarChar, 32) { Value = values[0] });
                    lsParm.Add(new SqlParameter("@FuncID" + parmIndex, SqlDbType.Int) { Value = ConvertUtil.ToInt(values[1]) });
                    parmIndex++;
                }
            }

            if (lsDelValue != null && lsDelValue.Count > 0)
            {
                string[] values = null;
                foreach (string item in lsDelValue)
                {
                    values = item.Split('_');
                    if (values.Length != 2)
                    {
                        return false;
                    }
                    sbSql.AppendFormat("delete PermissionList where MenuID=@MenuID{0} and FuncID=@FuncID{0} and RoleId=@RoleId;"
                    , parmIndex);
                    lsParm.Add(new SqlParameter("@MenuID" + parmIndex, SqlDbType.NVarChar, 32) { Value = values[0] });
                    lsParm.Add(new SqlParameter("@FuncID" + parmIndex, SqlDbType.Int) { Value = ConvertUtil.ToInt(values[1]) });
                    parmIndex++;
                }
            }
            if (sbSql.Length == 0)
            {
                return false;
            }
            lsParm.Add(new SqlParameter("@RoleId", SqlDbType.Int) { Value = roleId });
            int rows = DbHelperSQL.ExecuteSql(sbSql.ToString(), lsParm.ToArray());
            return rows > 0;
        }
        /// <summary>
        /// 更新权限控制（添加了PUserId字段）
        /// </summary>
        /// <param name="lsNewValue">新增权限值列</param>
        /// <param name="lsDelValue">删除权限值列</param>
        /// <param name="roleId">涉及角色编号</param>
        /// <returns></returns>
        public bool UpdatePermission2(List<string> lsNewValue, List<string> lsDelValue, int roleId)
        {
            List<SqlParameter> lsParm = new List<SqlParameter>();
            StringBuilder sbSql = new StringBuilder();
            int parmIndex = 0;

            if (lsNewValue != null && lsNewValue.Count > 0)
            {
                string[] values = null;
                foreach (string item in lsNewValue)
                {
                    values = item.Split('_');
                    if (values.Length != 3)
                    {
                        return false;
                    }
                    sbSql.AppendFormat("if not exists(select * from PermissionList where MenuID=@MenuID{0} and FuncID=@FuncID{0} and RoleId=@RoleId and PUserId=@PUserId{0}) insert into PermissionList (MenuID,FuncID,RoleId,PUserId) values(@MenuID{0},@FuncID{0},@RoleId,@PUserId{0});"
                    , parmIndex);
                    lsParm.Add(new SqlParameter("@MenuID" + parmIndex, SqlDbType.NVarChar, 32) { Value = values[0] });
                    lsParm.Add(new SqlParameter("@FuncID" + parmIndex, SqlDbType.Int) { Value = ConvertUtil.ToInt(values[1]) });
                    lsParm.Add(new SqlParameter("@PUserId" + parmIndex, SqlDbType.NVarChar, 32) { Value = values[2] });
                    parmIndex++;
                }
            }

            if (lsDelValue != null && lsDelValue.Count > 0)
            {
                string[] values = null;
                foreach (string item in lsDelValue)
                {
                    values = item.Split('_');
                    if (values.Length != 3)
                    {
                        return false;
                    }
                    sbSql.AppendFormat("delete PermissionList where MenuID=@MenuID{0} and FuncID=@FuncID{0} and RoleId=@RoleId and PUserId=@PUserId{0};"
                    , parmIndex);
                    lsParm.Add(new SqlParameter("@MenuID" + parmIndex, SqlDbType.NVarChar, 32) { Value = values[0] });
                    lsParm.Add(new SqlParameter("@FuncID" + parmIndex, SqlDbType.Int) { Value = ConvertUtil.ToInt(values[1]) });
                    lsParm.Add(new SqlParameter("@PUserId" + parmIndex, SqlDbType.NVarChar,32) { Value = values[2] });
                    parmIndex++;
                }
            }
            if (sbSql.Length == 0)
            {
                return false;
            }
            lsParm.Add(new SqlParameter("@RoleId", SqlDbType.Int) { Value = roleId });
            int rows = DbHelperSQL.ExecuteSql(sbSql.ToString(), lsParm.ToArray());
            return rows > 0;
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int RoleId, string MenuID, int FuncID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from PermissionList ");
            strSql.Append(" where RoleId=@RoleId and MenuID=@MenuID and FuncID=@FuncID ");
            SqlParameter[] parameters = {
					new SqlParameter("@RoleId", SqlDbType.Int,4),
					new SqlParameter("@MenuID", SqlDbType.NVarChar,32),
					new SqlParameter("@FuncID", SqlDbType.Int,4)			};
            parameters[0].Value = RoleId;
            parameters[1].Value = MenuID;
            parameters[2].Value = FuncID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Permission GetModel(int RoleId, string MenuID, int FuncID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 RoleId,MenuID,FuncID,PerStatus from PermissionList ");
            strSql.Append(" where RoleId=@RoleId and MenuID=@MenuID and FuncID=@FuncID ");
            SqlParameter[] parameters = {
					new SqlParameter("@RoleId", SqlDbType.Int,4),
					new SqlParameter("@MenuID", SqlDbType.NVarChar,32),
					new SqlParameter("@FuncID", SqlDbType.Int,4)			};
            parameters[0].Value = RoleId;
            parameters[1].Value = MenuID;
            parameters[2].Value = FuncID;

            Permission model = new Permission();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                
                return model;
            }
            else
            {
                return null;
            }
        }

        
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM PermissionList where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                }
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, List<string> fields, List<object> values, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append("  * ");
            strSql.Append(" FROM PermissionList where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    parm.Add(new SqlParameter("@" + fields[i], values[i]));
                }
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM PermissionList where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                }
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parm.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(List<string> fields, List<object> values, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.FuncID desc");
            }
            strSql.Append(")AS Row, T.*  from PermissionList T ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (i > 0)
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    else
                    {
                        strSql.Append(" where " + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i], values[i]));
                }
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }

        public string GetControlStr(int roleId, string parentId)
        {
            /*
            //页面编号
            List<string> list = new List<string>() { "010102", "010103", "010104", "010105", "010106"
                                                   , "0102", "010201", "010202", "010203", "010204"
                                                   , "010205", "010206", "010207", "010401", "0112"
                                                   , "011201", "011202", "011205", "0509", "0510", "0513"};
            if (roleId == (int)RoleType.平台客服 || roleId == (int)RoleType.网吧客服 || roleId == (int)RoleType.连锁公司客服)
            {
                list = new List<string>() { "0204", "0205", "0206", "0207" };
            }
            StringBuilder result = new StringBuilder();
            string temp = "";
            for (int i = 0; i < list.Count; i++)
            {
                temp = "";
                temp = @"   if not exists(select * from PermissionList where RoleId='" + roleId + "' and MenuID='" + list[i] + "' and PUserId='" + parentId + @"')
                            begin 
                            insert into PermissionList(RoleId,MenuID,FuncID,PerStatus,PUserId,createtime)
                            values('" + roleId + "','" + list[i] + "','1','1','" + parentId + @"',GETDATE())
                            insert into PermissionList(RoleId,MenuID,FuncID,PerStatus,PUserId,createtime)
                            values('" + roleId + "','" + list[i] + "','2','1','" + parentId + @"',GETDATE())
                            insert into PermissionList(RoleId,MenuID,FuncID,PerStatus,PUserId,createtime)
                            values('" + roleId + "','" + list[i] + "','3','1','" + parentId + @"',GETDATE())
                            insert into PermissionList(RoleId,MenuID,FuncID,PerStatus,PUserId,createtime)
                            values('" + roleId + "','" + list[i] + "','4','1','" + parentId + @"',GETDATE())
                            end
                            else 
                            update PermissionList set PerStatus='1' where RoleId='" + roleId + "' and MenuID='" + list[i] + "' and PUserId='" + parentId + "'       ";


                result.Append(temp);
            }

            return result.ToString();*/
            return "";
        }

        /// <summary>
        /// 增加一条数据(添加了PUserId后的添加方法)
        /// </summary>
        public bool Add2(Permission model)
        {
            /*
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into PermissionList(");
            strSql.Append("RoleId,MenuID,FuncID,PerStatus,PUserId)");
            strSql.Append(" values (");
            strSql.Append("@RoleId,@MenuID,@FuncID,@PerStatus,@PUserId)");
            SqlParameter[] parameters = {
					new SqlParameter("@RoleId", SqlDbType.Int,4),
					new SqlParameter("@MenuID", SqlDbType.NVarChar,32),
					new SqlParameter("@FuncID", SqlDbType.Int,4),
					new SqlParameter("@PerStatus", SqlDbType.Int,4),
					new SqlParameter("@PUserId", SqlDbType.NVarChar,32)};
            parameters[0].Value = model.RoleId;
            parameters[1].Value = model.MenuID;
            parameters[2].Value = model.FuncID;
            parameters[3].Value = model.PerStatus;
            parameters[4].Value = model.PUserId;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                
            }*/
            return false;
        }
        /// <summary>
        /// 批量添加权限信息的方法
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public int AddList(List<Permission> list)
        {
            /*
            if(list == null || list.Count <= 0)
            {
                
            }

            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();

            for (int i = 0; i < list.Count; i++)
            {
                strSql.Append(@"  insert into PermissionList(RoleId,MenuID,FuncID,PerStatus,PUserId) 
                                values (@RoleId" + i + ",@MenuID" + i + ",@FuncID" + i + ",@PerStatus" + i + ",@PUserId" + i + ")  ");
                parm.Add(new SqlParameter("@RoleId" + i, list[i].RoleId));
                parm.Add(new SqlParameter("@MenuID" + i, list[i].MenuID));
                parm.Add(new SqlParameter("@FuncID" + i, list[i].FuncID));
                parm.Add(new SqlParameter("@PerStatus" + i, list[i].PerStatus));
                parm.Add(new SqlParameter("@PUserId" + i, list[i].PUserId));
                strSql.Append("        ");
            }

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parm.ToArray());
            return rows;
            
            */
            return 0;
        }

        /// <summary>
        /// 网吧连锁给下级设置权限提取权限时用。(后期改进型，添加了PUserId字段)(一定要有PUserid)
        /// </summary>
        /// <param name="fields">最好加上RoleId和UserId作为限制</param>
        /// <param name="values"></param>
        /// <returns></returns>
        public DataSet GetListUnionMenu2(List<string> fields, List<object> values)
        {
            //计数器部分
            int sign = 0;
            string fie = "";
            List<string> list = new List<string>();
            //计数器部分结束
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();

            //            strSql1.Append(@" select * from (
            //                            select *,'' as PUserId from (
            //                            select RoleId,MenuID,FuncID,PerStatus from PermissionList where 1=1 and PUserId='' 
            //                            except
            //                            select RoleId,MenuID,FuncID,PerStatus from PermissionList where 1=1 and PUserId!=''
            //                            ) as t
            //                            union  ");

            strSql.Append(@"  ) as ttt 
                                left join Menu m on ttt.MenuID=m.MenuID
                                left join Menu mp on mp.MenuID=m.ParentMenu
                                ) as t
                                where 1=1  ");

            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    fie = fields[i].Substring(fields[i].IndexOf('_') + 1);
                    sign = GetNum(list, fie);
                    list.Add(fie);

                    if (fields[i].StartsWith("not_"))
                    {
                        strSql.Append(" and " + fie + " !=@" + fie + (sign == 0 ? " " : sign + " "));
                    }
                    else if (fields[i].StartsWith("PUserId"))
                    {

                        strSql1.Append(@" select * from (
                            select ttt.*,m.MenuLevel,m.MenuName,m.MenuUrl,m.ParentMenu,m.PicName,m.SortId,m.Status,mp.MenuName as PMenuName  from (
                            select *,0 as PerStatus,'' as PUserId from (
                            select RoleId,MenuID from PermissionList where 1=1 and PUserId=''  group by  RoleId,MenuID
                              except
                            select RoleId,MenuID from PermissionList where 1=1 and PUserId=@PUserId" + (sign == 0 ? " " : sign + " ")
                             + @"  group by  RoleId,MenuID
                            ) as t
                            union  
                            select RoleId,MenuID,PerStatus,PUserId from PermissionList where 1=1 and PUserId=@PUserId" + (sign == 0 ? " " : sign + " ")
                            + " group by  RoleId,MenuID,PerStatus,PUserId ");
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        strSql.Append(" and " + fie + " >=@" + fie + (sign == 0 ? " " : sign + " "));
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        strSql.Append(" and " + fie + " <=@" + fie + (sign == 0 ? " " : sign + " "));
                    }
                    else
                    {
                        strSql.Append(" and " + fie + "=@" + fie + (sign == 0 ? " " : sign + " "));
                    }
                    parm.Add(new SqlParameter("@" + fie + (sign == 0 ? " " : sign + " "), values[i]));
                }
            }
            strSql1.Append(strSql);
            return DbHelperSQL.Query(strSql1.ToString(), parm.ToArray());

        }

        //计数器辅助方法
        public int GetNum(List<string> list, string sign)
        {
            int num = 0;
            if (list == null || list.Count <= 0)
            {
                return 0;
            }
            for (int i = 0; i < list.Count; i++)
            {
                if ((!string.IsNullOrEmpty(list[i])) && list[i] == sign)
                {
                    num++;
                }
            }
            return num;
        }

        //计数器辅助方法结束



        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(List<string> fields, List<object> values)
        {

            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();
            strSql.Append("delete from PermissionList ");
            strSql.Append(" where 1=1 ");
            for (int i = 0; i < fields.Count; i++)
            {
                if (fields[i].StartsWith("like_"))
                {
                    strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                }
                else if (fields[i].StartsWith("not_"))
                {
                    strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                }
                else if (fields[i].StartsWith("lt_"))
                {
                    strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                }
                else if (fields[i].StartsWith("gt_"))
                {
                    strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                }
                else
                {
                    strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                }
                parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
            }


            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parm.ToArray());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool DeleteUnionMenu(List<string> fields, List<object> values)
        {

            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();
            strSql.Append(@" delete from PermissionList 
                            where MenuID not in 
                            (
                            select MenuID from Menu 
                            where Status=2
                            )   ");
            for (int i = 0; i < fields.Count; i++)
            {
                if (fields[i].StartsWith("like_"))
                {
                    strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                }
                else if (fields[i].StartsWith("not_"))
                {
                    strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                }
                else if (fields[i].StartsWith("lt_"))
                {
                    strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                }
                else if (fields[i].StartsWith("gt_"))
                {
                    strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                }
                else
                {
                    strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                }
                parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
            }


            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parm.ToArray());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 网吧连锁给下级设置权限提取权限时用。
        /// </summary>
        /// <param name="fields"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public DataSet GetListUnionMenu1(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();

            strSql.Append(@"  select * from (                       
                            select RoleId,p.MenuID,PerStatus,m.MenuLevel,m.MenuName,m.MenuUrl,m.ParentMenu,m.PicName,m.SortId,m.Status from PermissionList p
                            left join Menu m on p.MenuID = m.MenuID
                            group by RoleId,p.MenuID,PerStatus,m.MenuLevel,m.MenuName,m.MenuUrl,m.ParentMenu,m.PicName,m.SortId,m.Status
                            )as t
                            where 1=1   ");
            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                }
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());

        }
        //主页快捷菜单
        public DataSet GetListUnionMenu(List<string> fields, List<object> values, List<string> list)
        {


            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();
            int type = 0;
            for (int m = 0; m < fields.Count; m++)
            {
                if (fields[m].StartsWith("RoleId"))
                {
                    type++;
                }
            }
            type++;
            if (type > 0)
            {
                #region 不是管理员时执行的方法
                strSql.Append(@" select * from (
                                select  m.*,p.RoleId,p.PerStatus  FROM Menu m
                                left join PermissionList p on m.MenuID=p.MenuID 
	                            group by m.MenuID,MenuUrl,MenuName,MenuLevel,ParentMenu,SortId,Status,PicName,RoleId,PerStatus
                                ) T
                                 where MenuID in (
                                 select distinct p.MenuID
                                from PermissionList p 
                                left join Menu m on p.MenuID=m.MenuID
                                )     ");
                if (fields != null && fields.Count > 0)
                {
                    for (int i = 0; i < fields.Count; i++)
                    {
                        if (fields[i].StartsWith("like_"))
                        {
                            strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                        }
                        else if (fields[i].StartsWith("not_"))
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                        }
                        else if (fields[i].StartsWith("lt_"))
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                        }
                        else if (fields[i].StartsWith("gt_"))
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                        }
                        else
                        {
                            strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                        }
                        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                    }
                }
                if (list.Count > 0)
                {
                    strSql.Append(" AND  MenuID in (");
                    for (int j = 0; j < list.Count; j++)
                    {
                        strSql.Append("'" + list[j] + "'");
                        if (j != list.Count - 1)
                        {
                            strSql.Append(",");
                        }
                    }
                    strSql.Append(")  ");
                }
                #endregion
            }
            else
            {
                #region 管理员的方法
                //strSql.Append(@"select * from Menu where 1=1  ");
                //if (list.Count > 0)
                //{
                //    strSql.Append(" AND  MenuID in (");
                //    for (int j = 0; j < list.Count; j++)
                //    {
                //        strSql.Append("'" + list[j] + "'");
                //        if (j != list.Count - 1)
                //        {
                //            strSql.Append(",");
                //        }
                //    }
                //    strSql.Append(")  ");
                //}

                //if (fields != null && fields.Count > 0)
                //{
                //    for (int i = 0; i < fields.Count; i++)
                //    {
                //        if (fields[i].StartsWith("like_"))
                //        {
                //            strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                //        }
                //        else if (fields[i].StartsWith("not_"))
                //        {
                //            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                //        }
                //        else if (fields[i].StartsWith("lt_"))
                //        {
                //            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                //        }
                //        else if (fields[i].StartsWith("gt_"))
                //        {
                //            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                //        }
                //        else
                //        {
                //            strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                //        }
                //        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                //    }
                //}

                #endregion
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }

        //主页快捷菜单（表permissionList添加了字段PUserId后的修改）
        public DataSet GetListUnionMenu2(List<string> fields, List<object> values, List<string> list, string parentId)
        {


            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();
            int type = 0;
            for (int m = 0; m < fields.Count; m++)
            {
                if (fields[m].StartsWith("RoleId"))
                {
                    type++;
                }
            }
            type++;
            if (type > 0)
            {
                #region 不是管理员时执行的方法
                strSql.Append(@" select * from (
                                select  m.*,p.RoleId,p.PerStatus  FROM Menu m
                                left join PermissionList p on m.MenuID=p.MenuID 
                                where (PerStatus &1 =1) and PUserId='" + parentId + @"'
	                            group by m.MenuID,MenuUrl,MenuName,MenuLevel,ParentMenu,SortId,Status,PicName,RoleId,PerStatus,PUserId
                                ) T
                                 where MenuID in (
                                 select distinct p.MenuID
                                from PermissionList p 
                                left join Menu m on p.MenuID=m.MenuID
                                where (PerStatus &1 =1) and PUserId='" + parentId + @"'
                                )     ");
                if (fields != null && fields.Count > 0)
                {
                    for (int i = 0; i < fields.Count; i++)
                    {
                        if (fields[i].StartsWith("like_"))
                        {
                            strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                        }
                        else if (fields[i].StartsWith("not_"))
                        {
                            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                        }
                        else if (fields[i].StartsWith("lt_"))
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                        }
                        else if (fields[i].StartsWith("gt_"))
                        {
                            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                        }
                        else
                        {
                            strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                        }
                        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                    }
                }
                if (list.Count > 0)
                {
                    strSql.Append(" AND  MenuID in (");
                    for (int j = 0; j < list.Count; j++)
                    {
                        strSql.Append("'" + list[j] + "'");
                        if (j != list.Count - 1)
                        {
                            strSql.Append(",");
                        }
                    }
                    strSql.Append(")  ");
                }
                #endregion
            }
            else
            {
                #region 管理员的方法
                //strSql.Append(@"select * from Menu where 1=1  ");
                //if (list.Count > 0)
                //{
                //    strSql.Append(" AND  MenuID in (");
                //    for (int j = 0; j < list.Count; j++)
                //    {
                //        strSql.Append("'" + list[j] + "'");
                //        if (j != list.Count - 1)
                //        {
                //            strSql.Append(",");
                //        }
                //    }
                //    strSql.Append(")  ");
                //}

                //if (fields != null && fields.Count > 0)
                //{
                //    for (int i = 0; i < fields.Count; i++)
                //    {
                //        if (fields[i].StartsWith("like_"))
                //        {
                //            strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                //        }
                //        else if (fields[i].StartsWith("not_"))
                //        {
                //            strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                //        }
                //        else if (fields[i].StartsWith("lt_"))
                //        {
                //            strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                //        }
                //        else if (fields[i].StartsWith("gt_"))
                //        {
                //            strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                //        }
                //        else
                //        {
                //            strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                //        }
                //        parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                //    }
                //}

                #endregion
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }


        /// <summary>
        /// 网吧连锁机构批量处理子角色的权限的方法
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public int UpdateList(List<Permission> list)
        {
            /*
            if (list == null || list.Count <= 0)
            {
                return 0;
            }
            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> param = new List<SqlParameter>();
            for (int i = 0; i < list.Count; i++)
            {
                strSql.Append("  update PermissionList  set PerStatus=@PerStatus" + i);
                strSql.Append("  where 1=1 and RoleId=@RoleId" + i + " and MenuID=@MenuID" + i );

                param.Add(new SqlParameter("@PerStatus" + i, list[i].PerStatus));
                param.Add(new SqlParameter("@RoleId" + i, list[i].RoleId));
                param.Add(new SqlParameter("@MenuID" + i, list[i].MenuID));

                strSql.Append("    ");
            }
            object obj = DbHelperSQL.ExecuteSql(strSql.ToString(), param.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }*/
            return 0;
        }

        /// 网吧连锁机构批量处理子角色的权限的方法
        //表PermissionList添加了字段PUserId后的修改
        public int UpdateList2(List<Permission> list)
        {
            /*
            if (list == null || list.Count <= 0)
            {
                return 0;
            }
            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();
            for (int i = 0; i < list.Count; i++)
            {
                strSql.Append(@"  if not exists (select * from PermissionList where RoleId=@RoleId" + i + "  and MenuID=@MenuID" + i + "  and PUserId=@PUserId" + i + @"  ) 
                                begin
                                insert into PermissionList(RoleId,MenuID,FuncID,PerStatus,PUserId) values(@RoleId" + i + " ,@MenuID" + i + " ,'1',@PerStatus" + i + " ,@PUserId" + i + @" )
                                insert into PermissionList(RoleId,MenuID,FuncID,PerStatus,PUserId) values(@RoleId" + i + " ,@MenuID" + i + " ,'2',@PerStatus" + i + " ,@PUserId" + i + @" )
                                insert into PermissionList(RoleId,MenuID,FuncID,PerStatus,PUserId) values(@RoleId" + i + " ,@MenuID" + i + " ,'3',@PerStatus" + i + " ,@PUserId" + i + @" )
                                insert into PermissionList(RoleId,MenuID,FuncID,PerStatus,PUserId) values(@RoleId" + i + " ,@MenuID" + i + " ,'4',@PerStatus" + i + " ,@PUserId" + i + @" )
                                end
                                else update PermissionList set PerStatus=@PerStatus" + i + "  where RoleId=@RoleId" + i + "  and MenuID=@MenuID" + i + "  and PUserId=@PUserId" + i + "       ");
                parm.Add(new SqlParameter("@RoleId" + i, list[i].RoleId ));
                parm.Add(new SqlParameter("@MenuID" + i, list[i].MenuID ));
                parm.Add(new SqlParameter("@PerStatus" + i, list[i].PerStatus ));
                parm.Add(new SqlParameter("@PUserId" + i, list[i].PUserId ));

                strSql.Append("         ");

            }

            //默认插入Menu表中Status为2的页面的权限
            string temp = GetControlStr(list[0].RoleId,list[0].PUserId);
            if (!string.IsNullOrEmpty(temp))
            {
                strSql.Append(temp);
            }
            //默认插入Menu表中Status为2的页面的权限

            object obj = DbHelperSQL.ExecuteSql(strSql.ToString(), parm.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
            */
            return 0;


        }

    }
}

